/*
 * 
 */
package rai.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import rai.dto.LangDTO;
import rai.dto.UserDataDTO;
import rai.util.Constants;

/**
 * Database Operation for Admin Action.
 * @author Vu
 *
 */
public class AdminActionDAO {
    /** Log property. */
    private static Logger log = Logger.getLogger("EditTakenPrjInfoDAO.start");
    
    public void edit_acc (Integer empID, String role) {
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        PreparedStatement pStat = null;
        log.debug("EmployeeInfoDAO.deleteCourse.start");
        try {
            conn = rai.util.Connection.getConnection();
            query = "update user set Role='"
                    + role
                    + "' where EmpID=" + empID;
            pStat = conn.prepareStatement(query);
            pStat.executeUpdate(query);
            
        } catch (final Exception sqlEx) {
            log.error("Edit Account" + sqlEx);
        } finally {
            try {
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AccessEmpData.getCourse" + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.deleteCourse.end");
        
    }
    private Integer genPass() {
        return (int)(Math.random()*90000 + 10000);
    }
    public Integer reset_pass (Integer empID) {
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        PreparedStatement pStat = null;
        Integer password = genPass();
        log.debug("EmployeeInfoDAO.deleteCourse.start");
        try {
            conn = rai.util.Connection.getConnection();
            query = "update user set Password='"
                    + password
                    + "' where EmpID=" + empID;
            pStat = conn.prepareStatement(query);
            pStat.executeUpdate(query);
            
        } catch (final Exception sqlEx) {
            log.error("Edit Account" + sqlEx);
        } finally {
            try {
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AccessEmpData.getCourse" + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.deleteCourse.end");
        return password;
    }
    
    public void add_acc () {
        
    }
    public void del_acc (String[] accID) {
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        PreparedStatement pStat = null;
        log.debug("EmployeeInfoDAO.deleteCourse.start");
        try {
            for(int i = 0 ; i < accID.length;i++) {
                // query command
                query = "delete from courseexp "
                        + "where EmpID=" + accID[i]; 
                        
                // get connection
                conn = rai.util.Connection.getConnection();
                // prepare statement
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
                query = "delete from departexp "
                    + "where EmpID=" + accID[i];
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
                query = "delete from projectexp "
                    + "where EmpID=" + accID[i];
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
                query = "delete from employee "
                    + "where EmpID=" + accID[i];
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
                query = "delete from user "
                    + "where EmpID=" + accID[i];
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
            }
        } catch (final Exception sqlEx) {
            log.error("Delete Course" + sqlEx);
        } finally {
            try {
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AccessEmpData.getCourse" + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.deleteCourse.end");
    }
    
    public List<UserDataDTO> getAccList() {
        UserDataDTO dto;
        final List<UserDataDTO> result = new ArrayList<UserDataDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;
        
        try {
            // query command
            query = "select u.EmpID, Username, Password, e.Name, LastLogin, Role "
                    + "from employee e, user u "
                    + "where e.empID = u.empID";
                    
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            
            while (rs.next()) {
                dto = new UserDataDTO();
                dto.setEmpID(rs.getInt("EmpID"));
                dto.setUsername(rs.getString("Username"));
                dto.setPassword(rs.getString("Password"));
                dto.setEmpName(rs.getString("Name"));
                dto.setLastlogin(rs.getString("LastLogin"));
                dto.setRole(rs.getString("Role"));
                result.add(dto);
            }

           
        } catch (final Exception sqlEx) {
            log.error("AdminActionDAO.getAcc " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AddActionDAO.getAcc " + sqlEx);
            }
        }

        return result;
    }
    

}
